Possible regression: setNull() usage changed from 7.4 to 8.0 and up - Mailing list pgsql-jdbc
From | Jeff Hubbach |
---|---|
Subject | Possible regression: setNull() usage changed from 7.4 to 8.0 and up |
Date | |
Msg-id | r02010500-1046-FFD4AE24CB4411DAA9C0000D93451082@[205.238.79.158] Whole thread Raw |
Responses |
Re: Possible regression: setNull() usage changed from 7.4 to
(Kris Jurka <books@ejurka.com>)
|
List | pgsql-jdbc |
In the process of upgrading an app, I came across a change in behavior of PreparedStatement.setNull(). The behavior of the driver for 7.3 and 7.4 is consistent, a call to: stmt.setNull(1,java.sql.Types.NULL); succeeds. However, in 8.0 and up (including the 8.2 dev driver), this call fails with a "Could not determine data type" error. The following calls also fail: stmt.setNull(1,java.sql.Types.OTHER); stmt.setObject(1,null); stmt.setObject(1,null,java.sql.Types.NULL); stmt.setObject(1,null,java.sql.Types.OTHER); Changelog for Version dev401 (2005-07-24): * Improve null handling. Allow setNull(1, Types.XXX) for ARRAY, NULL, DISTINCT, and STRUCT by mapping them to the unknown oid. Changelog for Version 8.0-310 (2005-02-02): * Implement the Describe Statement protocol message. Reallow untyped nulls and get the backend to resolve the type for us (if necessary). Changelog for Version 8.0beta1-308 (2004-11-09): * Disallow these cases as we have insufficient type information: setNull(i,Types.OTHER), setObject(i,null), setObject(i,null,Types.OTHER). Similar thread on the mailing list archives: http://archives.postgresql.org/pgsql-jdbc/2005-02/msg00144.php JDBC Drivers tested: pg73jdbc3.jar pg74.216.jdbc3.jar postgresql-8.1-315.jdbc3.jar postgresql-8.2dev-501.jdbc3.jar Server Version: PostgreSQL 8.1.3 Stack Trace (using postgresql-8.1-315.jdbc3.jar): Exception in thread "main" org.postgresql.util.PSQLException: ERROR: could not determine data type of parameter $1 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse( QueryExecutorImpl.java:1512) at org.postgresql.core.v3.QueryExecutorImpl.processResults( QueryExecutorImpl.java:1297) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java: 188) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute( AbstractJdbc2Statement.java:437) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags( AbstractJdbc2Statement.java:353) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery( AbstractJdbc2Statement.java:257) at JDBCTest.main(JDBCTest.java:20) Test Case: createTestTable.sql =================== create table test_null (test_null integer); JDBCTest.java ============= import java.sql.*; public class JDBCTest { public static void main(String[] args) throws Exception { String url = "jdbc:postgresql://***/***"; String uname = "***"; String pword = "***"; Class.forName("org.postgresql.Driver"); Connection conn = DriverManager.getConnection(url,uname,pword); PreparedStatement st = conn.prepareStatement("select count(*) from test_null where ? is null"); st.setNull(1,java.sql.Types.NULL); // Fails //st.setNull(1,java.sql.Types.OTHER); // Fails //st.setNull(1,java.sql.Types.INTEGER); // Works //st.setObject(1,null); // Fails //st.setObject(1,null,java.sql.Types.NULL); // Fails //st.setObject(1,null,java.sql.Types.OTHER); // Fails //st.setString(1,null); // Works ResultSet rs = st.executeQuery(); while(rs.next()) { System.out.print("Column 1 returned "); System.out.println(rs.getString(1)); } rs.close(); st.close(); conn.close(); } }
pgsql-jdbc by date: